using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Common = Microsoft.ConnectionEngine.Registers.Health.Patient.Common;

namespace Microsoft.ConnectionEngine.Registers.Health.Patient.DataAccess
{

    public class Patient
    {

        #region Select Search Methods

        /// <summary>
        /// Return Patient Search Results
        /// </summary>
        /// <returns>Return an array of Patients</returns>
        public static Common.DataSets.dsPatient PatientSearch(string sPatientID,
                                                                string sAddressLine1,
                                                                string sAddressTownCity,
                                                                string sAddressCountry,
                                                                string sAddressCode,
                                                                string sTitle,
                                                                string sFamilyName,
                                                                string sGivenName1,
                                                                string sGivenName2,
                                                                string sGivenName3,
                                                                DateTime dDateOfBirth,
                                                                DateTime dDateOfDeath,
                                                                string sGender
                                                                )
        {
            string SQL = DataAccessResources.PatientSearchProc;
            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());

            SqlCommand dbCommand = new SqlCommand();
            dbCommand.CommandText = SQL;
            dbCommand.CommandType = CommandType.StoredProcedure;

  	        dbCommand.Parameters.AddWithValue("@PatientID", DataAccessHelper.IfNull(sPatientID ));
	        dbCommand.Parameters.AddWithValue("@AddressLine1", DataAccessHelper.IfNull(sAddressLine1 ));
	        dbCommand.Parameters.AddWithValue("@AddressTownCity", DataAccessHelper.IfNull(sAddressTownCity ));
            dbCommand.Parameters.AddWithValue("@AddressCountry", DataAccessHelper.IfNull(sAddressCountry ));
            dbCommand.Parameters.AddWithValue("@AddressCode", DataAccessHelper.IfNull(sAddressCode ));
	        dbCommand.Parameters.AddWithValue("@Title", DataAccessHelper.IfNull(sTitle ));
	        dbCommand.Parameters.AddWithValue("@FamilyName", DataAccessHelper.IfNull(sFamilyName ));
	        dbCommand.Parameters.AddWithValue("@GivenName1", DataAccessHelper.IfNull(sGivenName1 ));
	        dbCommand.Parameters.AddWithValue("@GivenName2", DataAccessHelper.IfNull(sGivenName2 ));
	        dbCommand.Parameters.AddWithValue("@GivenName3", DataAccessHelper.IfNull(sGivenName3 ));
	        dbCommand.Parameters.AddWithValue("@DateOfBirth", DataAccessHelper.IfNull(dDateOfBirth ));
            dbCommand.Parameters.AddWithValue("@DateOfDeath", DataAccessHelper.IfNull(dDateOfDeath ));
            dbCommand.Parameters.AddWithValue("@Gender", DataAccessHelper.IfNull(sGender ));

            Common.DataSets.dsPatient dsPat = new Common.DataSets.dsPatient();

            SqlDataReader sqlReader = (SqlDataReader)db.ExecuteReader(dbCommand);

            dsPat.Patient.Load(sqlReader, LoadOption.OverwriteChanges);
            dsPat.Names.Load(sqlReader, LoadOption.OverwriteChanges);

            return dsPat;

        }

        /// <summary>
        /// Return a selected Patient 
        /// </summary>
        /// <returns>Return a Patient</returns>
        public static Common.DataSets.dsPatient PatientSelect(string sPatientID)
        {
            string SQL = DataAccessResources.PatientSelectProc;
            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());

            SqlCommand dbCommand = new SqlCommand();
            dbCommand.CommandText = SQL;
            dbCommand.CommandType = CommandType.StoredProcedure;

            dbCommand.Parameters.AddWithValue("@PatientID", DataAccessHelper.IfNull(sPatientID));

            Common.DataSets.dsPatient dsPat = new Common.DataSets.dsPatient();
            SqlDataReader sqlReader = (SqlDataReader)db.ExecuteReader(dbCommand);

            dsPat.Patient.Load(sqlReader, LoadOption.OverwriteChanges);
            dsPat.Names.Load(sqlReader, LoadOption.OverwriteChanges);
            
            return dsPat;

        }

        #endregion

        #region Locking Methods

        /// <summary>
        /// Checks a Patient Record is locked
        /// </summary>
        /// <returns>Boolean indicating if record was locked or not</returns>
        public static bool IsLocked(string sPatientID, string sUserID)
        {
            string SQL = DataAccessResources.PatientIsLockedProc;
            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());

            SqlCommand dbCommand = new SqlCommand();
            dbCommand.CommandText = SQL;
            dbCommand.CommandType = CommandType.StoredProcedure;

            dbCommand.Parameters.AddWithValue("@PatientID", DataAccessHelper.IfNull(sPatientID));
            dbCommand.Parameters.AddWithValue("@UserID", DataAccessHelper.IfNull(sUserID));

            if (Convert.ToInt32(db.ExecuteScalar(dbCommand)) == 1)
                return true;
            else
                return false;
        }


        /// <summary>
        /// Locks a Patient Record
        /// </summary>
        /// <returns>Boolean indicating if record was locked or not</returns>
        public static bool PatientLock(string sPatientID, string sUserID)
        {
            string SQL = DataAccessResources.PatientLockProc;
            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());

            SqlCommand dbCommand = new SqlCommand();
            dbCommand.CommandText = SQL;
            dbCommand.CommandType = CommandType.StoredProcedure;

            dbCommand.Parameters.AddWithValue("@PatientID", DataAccessHelper.IfNull(sPatientID));
            dbCommand.Parameters.AddWithValue("@UserID", DataAccessHelper.IfNull(sUserID));

            if (db.ExecuteNonQuery(dbCommand) != 1)
                return false;
            else
                return true;
        }

        /// <summary>
        /// Unlocks a Patient Record
        /// </summary>
        /// <returns>Boolean indicating if record was unlocked or not</returns>
        public static bool PatientUnlock(string sPatientID, string sUserID)
        {
            string SQL = DataAccessResources.PatientUnlockProc;
            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());

            SqlCommand dbCommand = new SqlCommand();
            dbCommand.CommandText = SQL;
            dbCommand.CommandType = CommandType.StoredProcedure;

            dbCommand.Parameters.AddWithValue("@PatientID", DataAccessHelper.IfNull(sPatientID));
            dbCommand.Parameters.AddWithValue("@UserID", DataAccessHelper.IfNull(sUserID));

            if (db.ExecuteNonQuery(dbCommand) != 1)
                return false;
            else
                return true;
        }

        /// <summary>
        /// Unlocks all Patient Records for that User
        /// </summary>
        /// <returns>Boolean indicating if stored proc was successful</returns>
        public static bool PatientUnlockAll(string sUserID)
        {
            string SQL = DataAccessResources.PatientUnlockAllProc;
            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());

            SqlCommand dbCommand = new SqlCommand();
            dbCommand.CommandText = SQL;
            dbCommand.CommandType = CommandType.StoredProcedure;

            dbCommand.Parameters.AddWithValue("@UserID", DataAccessHelper.IfNull(sUserID));

            db.ExecuteNonQuery(dbCommand);

            return true;
        }

        #endregion

        #region Update/Insert

        /// <summary>
        /// Inserts a New Patient
        /// </summary>
        /// <returns>Returns a Patient ID</returns>
        public static string PatientInsert(string sPatientID,
                                                                int iPersonID,
                                                                bool bDonorFlag,
                                                                string sAddressLine1,
                                                                string sAddressLine2,
                                                                string sAddressSuburb,
                                                                string sAddressTownCity,
                                                                string sAddressCountry,
                                                                string sAddressCode,
                                                                string sUserID
                                                                )
        {
            string SQL = DataAccessResources.PatientInsertProc;
            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());

            SqlCommand dbCommand = new SqlCommand();
            dbCommand.CommandText = SQL;
            dbCommand.CommandType = CommandType.StoredProcedure;

            dbCommand.Parameters.AddWithValue("@PatientID", DataAccessHelper.IfNull(sPatientID));
		    dbCommand.Parameters.AddWithValue("@PersonID", DataAccessHelper.IfNull(iPersonID));
		    dbCommand.Parameters.AddWithValue("@DonorFlag", DataAccessHelper.IfNull(bDonorFlag));
		    dbCommand.Parameters.AddWithValue("@AddressLine1", DataAccessHelper.IfNull(sAddressLine1));
		    dbCommand.Parameters.AddWithValue("@AddressLine2", DataAccessHelper.IfNull(sAddressLine2));
		    dbCommand.Parameters.AddWithValue("@AddressSuburb", DataAccessHelper.IfNull(sAddressSuburb));
		    dbCommand.Parameters.AddWithValue("@AddressTownCity", DataAccessHelper.IfNull(sAddressTownCity));
		    dbCommand.Parameters.AddWithValue("@AddressCountry", DataAccessHelper.IfNull(sAddressCountry));
		    dbCommand.Parameters.AddWithValue("@AddressCode", DataAccessHelper.IfNull(sAddressCode));
            dbCommand.Parameters.AddWithValue("@UpdateUserID", DataAccessHelper.IfNull(sUserID));

            string sNewPatientID = Convert.ToString(db.ExecuteScalar(dbCommand));

            return sNewPatientID;

        }

        /// <summary>
        /// Updates a current Patient
        /// </summary>
        /// <returns>Returns boolean, set to True if patient was found and updated</returns>
        public static bool PatientUpdate(string sPatientID,
                                                                bool bDonorFlag,
                                                                string sAddressLine1,
                                                                string sAddressLine2,
                                                                string sAddressSuburb,
                                                                string sAddressTownCity,
                                                                string sAddressCountry,
                                                                string sAddressCode,
                                                                string sUserID
                                                                )
        {
            string SQL = DataAccessResources.PatientUpdateProc;
            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());

            SqlCommand dbCommand = new SqlCommand();
            dbCommand.CommandText = SQL;
            dbCommand.CommandType = CommandType.StoredProcedure;

            dbCommand.Parameters.AddWithValue("@PatientID", DataAccessHelper.IfNull(sPatientID));
            dbCommand.Parameters.AddWithValue("@DonorFlag", DataAccessHelper.IfNull(bDonorFlag));
            dbCommand.Parameters.AddWithValue("@AddressLine1", DataAccessHelper.IfNull(sAddressLine1));
            dbCommand.Parameters.AddWithValue("@AddressLine2", DataAccessHelper.IfNull(sAddressLine2));
            dbCommand.Parameters.AddWithValue("@AddressSuburb", DataAccessHelper.IfNull(sAddressSuburb));
            dbCommand.Parameters.AddWithValue("@AddressTownCity", DataAccessHelper.IfNull(sAddressTownCity));
            dbCommand.Parameters.AddWithValue("@AddressCountry", DataAccessHelper.IfNull(sAddressCountry));
            dbCommand.Parameters.AddWithValue("@AddressCode", DataAccessHelper.IfNull(sAddressCode));
            dbCommand.Parameters.AddWithValue("@UpdateUserID", DataAccessHelper.IfNull(sUserID));

            int iRowCount = db.ExecuteNonQuery(dbCommand);

            return (iRowCount == 1);

        }


        #endregion

    }
}
